﻿using CallCenter.Data.Base;
using CallCenter.Data.BizUtilities.Base;
using CallCenter.Data.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace CallCenter.Data.BizUtilities
{
   public  class CompanyOfficeUtility: BaseUtility
    {
        public CompanyOfficeUtility(DataManager manager)
            :base(manager)
        { }


        public ReturnMessage<CompanyOfficeViewModel> InsertToCompany(CompanyOfficeViewModel model)
        {
            var returnMessage = new ReturnMessage<CompanyOfficeViewModel>();
            if (String.IsNullOrEmpty(model.description))
                model.description = "";
            if (String.IsNullOrEmpty(model.faxNumber))
                model.faxNumber = "";
            if (String.IsNullOrEmpty(model.address))
                model.address = "";
            if (String.IsNullOrEmpty(model.city))
                model.city = "";
            if (String.IsNullOrEmpty(model.zip))
                model.zip = "";

            if (String.IsNullOrEmpty(model.phone))
                model.phone = "";

            if (String.IsNullOrEmpty(model.country))
                model.country = "";
            if (String.IsNullOrEmpty(model.timeZone))
                model.timeZone = "";

            if (String.IsNullOrEmpty(model.notes))
                model.notes = "";
         
            var id = this.Insert(model.description, model.name, model.companyId, model.address, model.city, model.stateId, model.zip,
     model.country, model.phone, model.faxNumber, model.dateStart, model.dateEnd, model.notes, model.timeZone);
          
            if (id == -1)
            {
                returnMessage.Message = "We cannot insert data to database";
                returnMessage.Status = false;
            }
            else
            {
                model.id = id;


                returnMessage.Message = "Recordhas been  inserted to database";
                returnMessage.Status = true;
            }

            return returnMessage;

        }
        public ReturnMessage<CompanyOfficeViewModel> UpdateToCompany(CompanyOfficeViewModel model)
        {
            var returnMessage = new ReturnMessage<CompanyOfficeViewModel>();

            if (String.IsNullOrEmpty(model.description))
                model.description = "";
            if (String.IsNullOrEmpty(model.faxNumber))
                model.faxNumber = "";
            if (String.IsNullOrEmpty(model.address))
                model.address = "";
            if (String.IsNullOrEmpty(model.city))
                model.city = "";
            if (String.IsNullOrEmpty(model.zip))
                model.zip = "";

            if (String.IsNullOrEmpty(model.phone))
                model.phone = "";

            if (String.IsNullOrEmpty(model.notes))
                model.notes = "";

            if (String.IsNullOrEmpty(model.country))
                model.country = "";

            if (String.IsNullOrEmpty(model.timeZone))
                model.timeZone = "";

            //change later
            //model.state = 4;

            var id = this.Update(model.id,model.description, model.name, model.companyId, model.address, model.city, model.stateId, model.zip,
     model.country, model.phone, model.faxNumber, model.dateStart, model.dateEnd, model.notes, model.timeZone);

            if (id == -1)
            {
                returnMessage.Message = "We cannot insert data to database";
                returnMessage.Status = false;
            }
            else
            {
                model.id = id;
                returnMessage.Status = true;
                returnMessage.Message = "Recordhas been  inserted to database";
            }

            return returnMessage;
        }


        public int Insert(string desc, string name, int companyId, string address, string city, int state, string zip,
                             string country, string phone, string fax, Nullable<DateTime> startDate, Nullable<DateTime> endDate, string notes, string timeZone)
        {
           
            int nRtn = -1;
           
            //!!!!!!!!!!!!!!!
            string commandString = "";
          
            //sql command to insert the data in the table ccContacts
            commandString = "Insert INTO ccCompanyOffice " +
                               "(description,name, companyId, address,city,state,zip,country,phone,faxNumber,dateStart,";

           
                commandString = commandString + " DateEnd,notes,timeZone) " +
                               "Values(@description,@name, @companyId, @address,@city,@state,@zip,@country,@phone,@faxNumber,@dateStart,@DateEnd,@notes,@timeZone) SELECT @pResult =@@IDENTITY ";
        

            using (var cmd = this.Factory.GetCommand(commandString))
            {
                this.Factory.AddParameter(cmd, "@name", SqlDbType.NVarChar, 50, name);
                this.Factory.AddParameter(cmd, "@description", SqlDbType.NVarChar, 50, desc);
                this.Factory.AddParameter(cmd, "@phone", SqlDbType.NVarChar, 50, phone);
                this.Factory.AddParameter(cmd, "@faxNumber", SqlDbType.NVarChar, 50, phone);
                this.Factory.AddParameter(cmd, "@address", SqlDbType.NVarChar, 10, address);

                this.Factory.AddParameter(cmd, "@city", SqlDbType.NVarChar, 1000, city);
                this.Factory.AddParameter(cmd, "@zip", SqlDbType.NVarChar, 100, zip);
                this.Factory.AddParameter(cmd, "@state", SqlDbType.Int, 10, state);
                this.Factory.AddParameter(cmd, "@country", SqlDbType.NVarChar, 50, country);

               
                this.Factory.AddParameter(cmd, "@notes", SqlDbType.VarChar, 100, notes);
                this.Factory.AddParameter(cmd, "@companyId", SqlDbType.Int, 10, companyId);

                this.Factory.AddParameter(cmd, "@timeZone", SqlDbType.NVarChar, 10, timeZone);


             
                    

                var param = this.Factory.AddParameter(cmd, "@pResult", System.Data.SqlDbType.NVarChar, 500, null);
                param.Direction = System.Data.ParameterDirection.Output;



                if ((startDate == null) || (startDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@dateStart", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@dateStart", SqlDbType.DateTime, 10, startDate);


                if ((endDate == null) || (endDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@DateEnd", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@DateEnd", SqlDbType.DateTime, 10, endDate);

                this.Factory.ExecuteNonQuery(cmd);


                nRtn = Convert.ToInt32(param.Value);

            }

            return nRtn; //retruning the data stored in the table

        }

        public int Update(int id, string desc, string name, int companyId, string address, string city, int state, string zip,
                        string country, string phone, string fax, Nullable<DateTime> startDate, Nullable<DateTime> endDate, string notes, string timeZone)
           
        {
          
            int nRtn = -1;
         
            //!!!!!!!!!!!!!!!!!1
            string commandString = "";
 
            commandString = "UPDATE ccCompanyOffice SET description=@description,name=@name, companyId=@companyId, address=@address,city=@city," +
                            "state=@state,zip=@zip,country=@country,phone=@phone,faxNumber=@faxNumber,dateStart=@dateStart, DateEnd = @DateEnd,";


               
         
            commandString = commandString + "notes=@notes,timeZone=@timeZone  WHERE id = @id";

            using (var cmd = this.Factory.GetCommand(commandString))
            {
                this.Factory.AddParameter(cmd, "@name", SqlDbType.NVarChar, 50, name);
                this.Factory.AddParameter(cmd, "@description", SqlDbType.NVarChar, 50, desc);
                this.Factory.AddParameter(cmd, "@phone", SqlDbType.NVarChar, 50, phone);
                this.Factory.AddParameter(cmd, "@faxNumber", SqlDbType.NVarChar, 50, phone);
                this.Factory.AddParameter(cmd, "@address", SqlDbType.NVarChar, 10, address);

                this.Factory.AddParameter(cmd, "@city", SqlDbType.NVarChar, 1000, city);
                this.Factory.AddParameter(cmd, "@zip", SqlDbType.NVarChar, 100, zip);
                this.Factory.AddParameter(cmd, "@state", SqlDbType.Int, 10, state);
                this.Factory.AddParameter(cmd, "@country", SqlDbType.NVarChar, 50, country);

               
                this.Factory.AddParameter(cmd, "@notes", SqlDbType.VarChar, 100, notes);
                this.Factory.AddParameter(cmd, "@companyId", SqlDbType.Int, 10, companyId);

                this.Factory.AddParameter(cmd, "@timeZone", SqlDbType.NVarChar, 10, timeZone);
                this.Factory.AddParameter(cmd, "@id", SqlDbType.Int, 10, id);
               

                if ((startDate == null) || (startDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@dateStart", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@dateStart", SqlDbType.DateTime, 10, startDate);


                if ((endDate == null) || (endDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@DateEnd", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@DateEnd", SqlDbType.DateTime, 10, endDate);

                this.Factory.ExecuteNonQuery(cmd);

                nRtn = id;
            }
                return nRtn; //retruning the data stored in the table

            }
        public void Delete(int id) //delete the row of data using the id passed to the function
        {
          string sql= "DELETE FROM ccCompanyOffice " +
                "WHERE ID = @ID ";


            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@ID", SqlDbType.Int, 10, id);
                this.Factory.ExecuteNonQuery(cmd);
            }

        }

        public DataTable GetAll()
        {string sql = "SELECT * FROM ccCompanyOffice";

            return this.Factory.ExecuteTable(sql);
        }

        public List<CompanyOfficeViewModel>GetAllGrid(int companyId)
        {string sql= "SELECT co.*, c.name as company, s.state as stateName  FROM ccCompanyOffice co " +
                                    "INNER JOIN ddlCompany c ON c.id = co.companyId " +
                                    "INNER JOIN ddlStates s ON s.id = co.state WHERE co.companyId = " + companyId;

            return this.Factory.ExecuteObjects<CompanyOfficeViewModel>(sql);
        }

        public List<CompanyOfficeViewModel> GetCompanyOffices(int companyId)
        {
            string sql = "SELECT 0 as id, 'None' as companyoffice UNION ALL SELECT id, cf.name as companyoffice FROM ccCompanyOffice cf";

            return this.Factory.ExecuteObjects<CompanyOfficeViewModel>(sql);
        }


        public List<CompanyOfficeViewModel> GetAllCompanyOffices()
        {
            string sql = "SELECT 0 as officeId, 'None' as companyoffice UNION ALL SELECT id as officeId, cf.name as companyoffice FROM ccCompanyOffice cf order by companyoffice ";

            return this.Factory.ExecuteObjects<CompanyOfficeViewModel>(sql);
        }


        public List<CompanyOfficeViewModel> GetCompanyOfficesByCompanyId(int companyId)
        {
            //string sql = "SELECT 0 as officeId, 'None' as companyoffice UNION ALL SELECT id as officeId, cf.name as companyoffice FROM ccCompanyOffice cf where companyId =  '" + companyId + "' order by companyoffice ";
            string sql = "SELECT id as officeId, cf.name as companyoffice FROM ccCompanyOffice cf where companyId =  '" + companyId + "' order by companyoffice ";

            return this.Factory.ExecuteObjects<CompanyOfficeViewModel>(sql);
        }







    }
}
